﻿using Avalonia;
using Avalonia.Controls;
using Avalonia.Controls.ApplicationLifetimes;
using Avalonia.Platform.Storage;
using CommunityToolkit.Mvvm.ComponentModel;
using CommunityToolkit.Mvvm.Input;
using Microsoft.Data.Sqlite;
using MsBox.Avalonia.Enums;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using 畜牧业审核程序国产.Models;
using 畜牧业审核程序国产.Models.DataGridMsgs;
using 畜牧业审核程序国产.Models.ToolsClass;

namespace 畜牧业审核程序国产.ViewModels
{
    internal partial class Page2ViewModel : ViewModelBase
    {
        #region 第一个下拉框,即畜种下拉框
        private ObservableCollection<KeyValuePair<string, string>> _animalReportCln = new ObservableCollection<KeyValuePair<string, string>> {
            new KeyValuePair<string, string>("猪", "审核公式A209"),
            new KeyValuePair<string, string>("牛", "审核公式A210"),
            new KeyValuePair<string, string>("羊", "审核公式A211"),
            new KeyValuePair<string, string>("家禽", "审核公式A212"),
            new KeyValuePair<string, string>("全面", "审核公式A406")
        };

        public ObservableCollection<KeyValuePair<string, string>> AnimalReportCln

        {
            get { return _animalReportCln; }
            //set
            //{
            //    SetProperty(ref _animalReportCln, value);
            //    ShowTheFormula();
            //}
        }
        private KeyValuePair<string, string> _selectedAnimalReport;

        public KeyValuePair<string, string> SelectedAnimalReport
        {
            get { return _selectedAnimalReport; }
            set
            {
                if (value.Value != null)
                {
                    SetProperty(ref _selectedAnimalReport, value);
                    //ShowTheFormula();
                    ReloadFormulaMsgs();
                    ReloadAddNewFormulaMsgs();
                    ResetDiscribeOfAddNewFormula(value.Key);
                }

            }
        }


        #endregion
        #region 第二个下拉框
        //private ObservableCollection<string> _formulaLogicalCln = new ObservableCollection<string>() { "表内", "环比", "与上季度逻辑", "暂时不审" };

        //public ObservableCollection<string> FormulaLogicalCln
        //{
        //    get { return _formulaLogicalCln; }//= new ObservableCollection<string>() { "表内", "环比", "与上季度逻辑" }; }

        //    // set => SetProperty(ref _formulaLogicalCln, value);
        //}
        //private string _selectedLogical;

        //public string SelectedLogical
        //{
        //    get { return _selectedLogical; }
        //    set
        //    {
        //        SetProperty(ref _selectedLogical, value);
        //       // ShowTheFormula();
        //    }
        //}
        //#endregion
        //#region 第三个下拉框(严重度)
        //private ObservableCollection<string> _formulaSeriesCln = new ObservableCollection<string>() { "强制性", "核实性" };

        //public ObservableCollection<string> FormulaSereiesCln
        //{
        //    get { return _formulaSeriesCln; }
        //    // set { _formulaSeries = value; }
        //}
        //private string? _selectedSeries;

        //public string? SelectedSeries
        //{
        //    get { return _selectedSeries; }
        //    set
        //    {
        //        SetProperty(ref _selectedSeries, value);
        //        ShowTheFormula();
        //    }
        //}

        //#endregion  
        //#region 第四个下拉框
        //private ObservableCollection<string> _allFormulaCln = new ObservableCollection<string>();

        //public ObservableCollection<string> AllFormulaCln
        //{
        //    get { return _allFormulaCln; }
        //    set => SetProperty(ref _allFormulaCln, value);
        //}
        //private string _selectedFormula;

        //public string SelectedFormula
        //{
        //    get { return _selectedFormula; }
        //    set => SetProperty(ref _selectedFormula, value);
        //}


        #endregion
        //private void ShowTheFormula()
        //{
        //    //if (this.SelectedAnimalReport.Value != null && SelectedLogical != null && SelectedSeries != null)
        //    //{

        //    //    string sqlToGetFormula = $"select * from {SelectedAnimalReport.Value} where 横向纵向 = '{SelectedLogical}' and 严重度 = '{SelectedSeries}'";
        //    //    MyDBOperator getFormulaDBOperator = new MyDBOperator();
        //    //    DataTable dtOfFormula = getFormulaDBOperator.GetDataTable(sqlToGetFormula);
        //    //    var AllFormulaInTheReport = dtOfFormula.AsEnumerable().Select(row => row.Field<string>("审核公式名称")).ToList();
        //    //    AllFormulaCln.Clear();
        //    //    foreach (var item in AllFormulaInTheReport)
        //    //    {
        //    //        AllFormulaCln.Add(item);
        //    //    }
        //    //}
        //    if (this.SelectedAnimalReport.Value != null && SelectedLogical != null)
        //    {

        //        string sqlToGetFormula = $"select * from {SelectedAnimalReport.Value} where 表间关系 = '{SelectedLogical}' ";
        //        MyDBOperator getFormulaDBOperator = new MyDBOperator();
        //        DataTable dtOfFormula = getFormulaDBOperator.GetDataTable(sqlToGetFormula);
        //        var AllFormulaInTheReport = dtOfFormula.AsEnumerable()
        //            //.Where(row=>string.IsNullOrEmpty( row.Field<string>("关键指标名称1").ToString())==false)
        //            .OrderBy(r => r.Field<string>("严重度"))//加入.Reverse()倒序
        //            .Select(row => row.Field<string>("严重度") + "--" + row.Field<string>("审核公式名称")).ToList();//.Select((row) => { return row.Field<string>("严重度") + "--" + row.Field<string>("审核公式名称"); }).ToList();
        //        AllFormulaCln.Clear();
        //        foreach (var item in AllFormulaInTheReport)
        //        {
        //            AllFormulaCln.Add(item);
        //        }
        //    }
        //}
        //public DataTable dtOfFormula = null;
        public void ReloadFormulaMsgs()
        {
            if (this.SelectedAnimalReport.Value != null)
            {
                DataTable dtOfFormula = null;
                string sqlToGetFormula = $"select * from {SelectedAnimalReport.Value} where [关键指标名称1] is not null ";
                //MyDBOperator getFormulaDBOperator = new MyDBOperator();改用单例模式
                MyDBOperator getFormulaDBOperator = MyDBOperator.DBOperatorInstance;
                dtOfFormula = getFormulaDBOperator.GetDataTable(sqlToGetFormula);
                var AllFormulaInTheReport = dtOfFormula.AsEnumerable()
                    .OrderBy(r => r.Field<string>("严重度"))
                    .ToArray();


                FormulaMsgsCln.Clear();

                for (int i = 0; i < AllFormulaInTheReport.Length; i++)
                {
                    DataRow row = AllFormulaInTheReport[i];
                    FormulaMsgs formulaMsgs = new FormulaMsgs(row.Field<string>("表间关系"), row.Field<string>("严重度"), row.Field<string>("是否启用"), row.Field<string>("审核公式名称"), row.Field<string>("审核公式说明"),
                    row.Field<string>("sql语句"), row.Field<string>("sql预备语句"), row.Field<string>("关键指标名称1"), row.Field<string>("关键指标值1"), row.Field<string>("关键指标名称2"), row.Field<string>("关键指标值2")
                      , row.Field<string>("审核公式说明预备语句"));
                    FormulaMsgsCln.Add(formulaMsgs);
                }
                ////加一条供用户修改的
                //FormulaMsgsCln.Add(new FormulaMsgs("新增公式"));
            }

        }


        #region fumulaMsgs
        private ObservableCollection<FormulaMsgs> _formulaMsgsCln = new ObservableCollection<FormulaMsgs>();

        public ObservableCollection<FormulaMsgs> FormulaMsgsCln
        {
            get { return _formulaMsgsCln; }
            set { SetProperty(ref _formulaMsgsCln, value); }
        }

        #endregion
        #region AddNewFormula
        [ObservableProperty]
        private ObservableCollection<AddNewFormulaMsgs> _addNewFormulaCln = new ObservableCollection<AddNewFormulaMsgs>();
        public void ReloadAddNewFormulaMsgs()
        {
            if (this.SelectedAnimalReport.Value != null)
            {
                string sqlToGetFormulasAddedByUser = $"select * from {SelectedAnimalReport.Value} where [用户新增] = '是'";
                // MyDBOperator getFormulasAddByUserOperator = new MyDBOperator();改用单例模式
                MyDBOperator getFormulasAddByUserOperator = MyDBOperator.DBOperatorInstance;
                DataTable dtOfFormulasAddByUser = getFormulasAddByUserOperator.GetDataTable(sqlToGetFormulasAddedByUser);
                AddNewFormulaCln.Clear();
                if (dtOfFormulasAddByUser != null && dtOfFormulasAddByUser.Rows.Count > 0)//如果用户已经自己新增过了,把用户新增的显示出来
                {

                    for (global::System.Int32 i = 0; i < dtOfFormulasAddByUser.Rows.Count; i++)
                    {
                        DataRow row = dtOfFormulasAddByUser.Rows[i];
                        AddNewFormulaMsgs addedFormulasByUser = new AddNewFormulaMsgs(row.Field<string>("表间关系"), row.Field<string>("严重度"),
                            row.Field<string>("是否启用"), row.Field<string>("审核公式名称"), row.Field<string>("审核公式说明"), row.Field<string>("sql语句"), row.Field<string>("用户新增"));
                        AddNewFormulaCln.Add(addedFormulasByUser);
                    }
                    AddNewFormulaCln.Add(new AddNewFormulaMsgs());
                }
                else
                {
                    AddNewFormulaCln.Add(new AddNewFormulaMsgs());
                }
            }


        }
        [ObservableProperty]
        private AddNewFormulaMsgs _selectedFormula;
        #endregion
        #region 审核公式新增界面显示的描述
        [ObservableProperty]
        private string _discribeRuleOfAddNewFormula = null;
        private void ResetDiscribeOfAddNewFormula(string animal)
        {
            if (animal.Equals("猪"))
            {
                DiscribeRuleOfAddNewFormula = "数据库中生猪规模户字段如下:省,市,市代码,县,县代码,乡,乡镇代码,村,户编码,户主姓名,养殖户单位类型,当季出栏数, 期末存栏合计01, 25公斤以下仔猪存栏数02," +
        "待育肥猪存栏数03,50公斤以上待育肥猪存栏数04,种猪存栏数05,能繁殖母猪存栏数06,当季增加头数07,自繁头数08,购进头数09," +
        "当季减少头数10,自宰肥猪头数11, 出售肥猪头数（不包括出售待育肥猪）12,出售肥猪金额（元）13," +
        "出售肥猪重量（公斤）14,其它原因减少（包括死亡、疫病捕杀）15,出售25公斤以下仔猪头数16,出售25公斤以下仔猪金额（元）17," +
        "出售25公斤以下仔猪重量（公斤）18" + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine+
        "A406表与生猪相关的字段为:市,市代码,县,县代码,乡,乡镇代码,生猪存栏头数（含未断奶小猪）01、能繁殖的母猪存栏头数02、生猪出栏头数03、猪肉产量（吨）14" + Environment.NewLine +
        "当季的数据、上季的数据和当季A406表在数据库中分别存储在名为“查询用表”、“环比用表”和“全面用表”的表中" + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine +
        "用户可以根据上述信息用sql语句编写新的查询公式,注意sql预计中的括号和逗号必须在英文状态下输入。字段若有括号,可以在字段两侧加上方括号,例如:" + Environment.NewLine +
        "①需要查找本季度当季增加头数不等于自繁加购进的养殖户,sql语句如下:select 县,乡,村,户主姓名, 当季增加头数07,自繁头数08,购进头数09 from 查询用表 where  当季增加头数07 <> (自繁头数08+购进头数09)" + Environment.NewLine +
        "②需要查找自繁比超过20的养殖户,sql语句如下:select t1.县,t1.乡,t1.村,t1.户主姓名,t1.能繁殖母猪存栏数06 as 本季度能繁殖母猪存栏数06,t1.自繁头数08 as 本季度自繁头数08,t2.自繁头数08 as 上季度自繁头数08, round((t1.自繁头数08+t2.自繁头数08)/t1.能繁殖母猪存栏数06) as 自繁比 from 查询用表 t1," +
        "环比用表 t2 where t1.户主姓名 = t2.户主姓名 and (t1.能繁殖母猪存栏数06)>0 and  (t1.自繁头数08+t2.自繁头数08)/t1.能繁殖母猪存栏数06 >20" + Environment.NewLine +
        "③如果要查找406表生猪肉产量少于规模户出售肥猪的毛重*60%(屠宰率)的乡镇,sql语句如下:" +
        "select t1.县,t1.乡,t1.[猪肉产量（吨）14] as [406表猪肉产量（吨）],t2.[规模户出售肥猪毛重合计（公斤）], round(t2.[规模户出售肥猪毛重合计（公斤）]*0.60/1000,2) as [以60%屠宰率计算的规模户猪肉产量(吨)] " +
        "from 全面用表 t1,(select 乡镇代码, sum([出售肥猪重量（公斤）14]) as [规模户出售肥猪毛重合计（公斤）] from 查询用表 group by 乡镇代码) t2 where t1.乡镇代码 = t2.乡镇代码 and(t1.[猪肉产量（吨）14] * 1000 < t2.[规模户出售肥猪毛重合计（公斤）] * 0.60)"
        ;
            }
            else if (animal.Equals("牛"))
            {
                DiscribeRuleOfAddNewFormula = "数据库中牛规模户字段如下:省,市,市代码,县,县代码,乡,乡镇代码,村,户编码,户主姓名,养殖户单位类型,当季出栏数,"+
        "期末存栏头数01,肉牛存栏数02,能繁殖母牛存栏数03,奶牛存栏数04,在产奶牛存栏数05,当季增加头数06,自繁头数07,繁肉牛头数08,"+
            "自繁奶牛头数09,购进头数10,购进肉牛头数11,购进奶牛头数12,期内减少头数13,"+
            "自宰肉牛数量14,出售肉牛数量15,出售肉牛金额（元）16,出售肉牛重量（公斤）17,当季生牛奶产量（公斤）18" + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine +
        "A406表与牛相关的字段为:市,市代码,县,县代码,乡,乡镇代码,牛存栏头数04、良种及改良种乳牛存栏头数05、牛出栏头数06、牛肉产量（吨）15、生牛奶产量（吨）21" + Environment.NewLine +
        "当季的数据、上季的数据和当季A406表在数据库中分别存储在名为“查询用表”、“环比用表”和“全面用表”的表中" + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine+
        "用户可以根据上述信息用sql语句编写新的查询公式,注意sql预计中的括号和逗号必须在英文状态下输入。字段若有括号,可以在字段两侧加上方括号,例如:" + Environment.NewLine +
        "①需要查找本季度无奶牛存栏但有牛奶产量的养殖户,sql语句如下:select 县,乡,村,户主姓名, 在产奶牛存栏数05 as 本季度在产奶牛存栏数05,[当季生牛奶产量（公斤）18] as [本季度当季生牛奶产量（公斤）18] from (select * from 查询用表 where [当季生牛奶产量（公斤）18]>0) where 在产奶牛存栏数05 =0 or 在产奶牛存栏数05 is null" + Environment.NewLine +
        "②需要查找牛存栏头数变动超过50%且数量超过100头的养殖户,sql语句如下:select t1.县,t1.乡,t1.村,t1.户主姓名,t1.期末存栏头数01 as 本季末存栏数, t2.期末存栏头数01 as 上季末存栏数   from 查询用表 t1,环比用表 t2 where t1.户编码 = t2.户编码 and t2.期末存栏头数01>0 and abs( t1.期末存栏头数01 / t2.期末存栏头数01-1)>0.5 and  t1.期末存栏头数01 - t2.期末存栏头数01>100" +
         Environment.NewLine +
        "③如果要查找406表牛肉产量少于规模户出售肉牛的毛重*50%(屠宰率)的乡镇,sql语句如下:" +
        "select t1.县,t1.乡, t1.[牛肉产量（吨）15] AS [406表牛肉产量（吨）], t2.[规模户出售肉牛毛重合计（公斤）], round(t2.[规模户出售肉牛毛重合计（公斤）]*0.5/1000,2) AS [以50%屠宰率计算的规模户牛肉产量（吨）] from 全面用表 t1,(select 乡镇代码,sum(出售肉牛重量（公斤）17) as [规模户出售肉牛毛重合计（公斤）] from  查询用表  group by 乡镇代码) t2 where t1.组织机构代码  = t2.乡镇代码 and (t1.[牛肉产量（吨）15]*1000 <t2.[规模户出售肉牛毛重合计（公斤）]*0.5)"
        ;

            }
            else if (animal.Equals("羊"))
            {
                DiscribeRuleOfAddNewFormula = "数据库中羊规模户字段如下:省,市,市代码,县,县代码,乡,乡镇代码,村,户编码,户主姓名,养殖户单位类型,"+
                   "当季出栏数,期末存栏只数01,山羊存栏只数02,能繁殖母山羊存栏只数03,绵(湖)羊存栏只数04,能繁殖母绵羊存栏只数05,期内增加只数06,"+
        "自繁只数07,山羊自繁只数08,绵(湖)羊自繁只数09,购进只数10,山羊购进只数11,绵(湖)羊购进只数12,期内减少只数13,自宰羊数量14,出售羊数量15,出售羊金额（元）16,出售羊重量（公斤）17,当季绵羊毛产量（公斤）18,"+
       "当季山羊绒产量（公斤）19 " + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine +
      "A406表与羊相关的字段为:市、市代码、县、县代码、乡、乡镇代码、羊存栏只数07、羊出栏只数08、羊肉产量（吨）16" + Environment.NewLine +
      "当季的数据、上季的数据和当季A406表在数据库中分别存储在名为“查询用表”、“环比用表”和“全面用表”的表中" + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine +
      "用户可以根据上述信息用sql语句编写新的查询公式,注意sql预计中的括号和逗号必须在英文状态下输入。字段若有括号,可以在字段两侧加上方括号。例如:" + Environment.NewLine +
      "①需要查找本季度期内增加的自繁羊数量不等于自繁山羊与自繁绵(湖)羊之和的养殖户,sql语句如下:select 县,乡,村,户主姓名,自繁只数07 as 本季度自繁只数07,[山羊自繁只数08] as [本季度山羊自繁只数08],[绵(湖)羊自繁只数09] as [本季度绵(湖)羊自繁只数09] from 查询用表 where 自繁只数07- [山羊自繁只数08]-[绵(湖)羊自繁只数09] <>0" + Environment.NewLine +
      "②需要期末存栏不等于(上季度存栏+本季期内增加-本季期内减少)的养殖户,sql语句如下:select t1.县,t1.乡,t1.村,t1.户主姓名, t1.期末存栏只数01 as 本季度期末存栏只数01,t1.期内增加只数06 as 本季度期内增加只数06, t1.期内减少只数13 as 本季度期内减少只数13,t2.期末存栏只数01 as 上季度期末存栏只数01 from 查询用表 t1,环比用表 t2 where t1.户编码= t2.户编码 and (t1.期末存栏只数01-t2.期末存栏只数01-t1.期内增加只数06+t1.期内减少只数13)<>0" + Environment.NewLine +
      "③如果要查找406表羊肉产量小于规模户羊出栏毛重*0.5(屠宰率)的乡镇,sql语句如下: select t1.县,t1.乡, t1.[羊肉产量（吨）16] AS [406表羊肉产量(吨)], t2.[规模户出售羊毛重合计(公斤)], round(t2.[规模户出售羊毛重合计(公斤)]*0.5/1000,2) AS [以50%屠宰率计算的规模户羊肉产量(吨)] from 全面用表 t1,(select 乡镇代码,sum(出售羊重量（公斤）17) as [规模户出售羊毛重合计(公斤)]    from  查询用表 group by 乡镇代码) t2  where t1.乡镇代码  = t2.乡镇代码 and (t1.[羊肉产量（吨）16]*1000 <t2.[规模户出售羊毛重合计(公斤)]*0.5)"
      ;

            }
            else if (animal.Equals("家禽"))
            {
                DiscribeRuleOfAddNewFormula = "数据库中家禽规模户字段如下:省,市,市代码,县,县代码,乡,乡镇代码,村,户编码,户主姓名,养殖户单位类型," +
                  "当季家禽出栏数,当季鸡出栏数,当季鸡存栏数,期末存栏只数01,肉用家禽存栏只数02,肉鸡存栏只数03,蛋用家禽存栏只数04,蛋鸡存栏只数05,期内减少只数06,自宰家禽数量07,"+
        "自宰活鸡数量08,出售家禽数量09,出售活鸡数量10,出售家禽金额（元）11,出售活鸡金额（元）12,出售家禽重量（公斤）13,出售活鸡重量（公斤）14,当季禽蛋产量（公斤）15,鸡蛋产量（公斤）16 " + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine +
     "A406表与家禽相关的字段为:市、市代码、县、县代码、乡、乡镇代码、家禽存栏只数09、家禽出栏只数10、禽肉产量（吨）17、鸡鸭鹅蛋产量（吨）20、鸡存栏只数23、鸡出栏只数24" + Environment.NewLine +
     "当季的数据、上季的数据和当季A406表在数据库中分别存储在名为“查询用表”、“环比用表”和“全面用表”的表中" + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine +
     "用户可以根据上述信息用sql语句编写新的查询公式,注意sql预计中的括号和逗号必须在英文状态下输入。字段若有括号,可以在字段两侧加上方括号,例如:" + Environment.NewLine +
     "①需要查找蛋禽平均产量大于10公斤的养殖户,sql语句如下:select 县,乡,村,户主姓名, [当季禽蛋产量（公斤）15] as [本季度当季禽蛋产量（公斤）15], 蛋用家禽存栏只数04 as 本季度蛋用家禽存栏只数04,round([当季禽蛋产量（公斤）15]/蛋用家禽存栏只数04,2) as [平均禽蛋产量（公斤/只）] from (select * from 查询用表  where 蛋用家禽存栏只数04 >0 and 蛋用家禽存栏只数04 is not null) where [当季禽蛋产量（公斤）15]/[蛋用家禽存栏只数04] >10" + Environment.NewLine +
     
     "②如果要查找406表禽肉产量小于规模户禽出栏毛重*0.65(屠宰率)的乡镇,sql语句如下:select t1.县,t1.乡, t1.[禽肉产量（吨）17] AS [406表禽肉产量(吨)], t2.[规模户禽出栏毛重合计(公斤)], round(t2.[规模户禽出栏毛重合计(公斤)]*0.65/1000,2) AS [以65%屠宰率计算的规模户禽肉产量(吨)] from 全面用表 t1,(select 乡镇代码,sum([出售家禽重量（公斤）13]) as [规模户禽出栏毛重合计(公斤)]   from  查询用表  group by 乡镇代码) t2 where t1.乡镇代码  = t2.乡镇代码 and (t1.[禽肉产量（吨）17]*1000 <t2.[规模户禽出栏毛重合计(公斤)]*0.65)" 
    ;
            }
            else if (animal.Equals("全面"))
            {
                DiscribeRuleOfAddNewFormula = "数据库中A406表的字段如下:市,市代码,县,县代码,乡,乡镇代码" +
                "生猪存栏头数（含未断奶小猪）01,能繁殖的母猪存栏头数02,生猪出栏头数03,牛存栏头数04,良种及改良种乳牛存栏头数05,牛出栏头数06,羊存栏只数07," +
      "羊出栏只数08,家禽存栏只数09,家禽出栏只数10,兔存栏只数11,兔出栏只数12,肉类产量13,猪肉产量（吨）14,牛肉产量（吨）15,羊肉产量（吨）16 " +
   "禽肉产量（吨）17,兔肉产量（吨）18,其他肉产量（吨）19,鸡鸭鹅蛋产量（吨）20,生牛奶产量（吨）21,蚕茧产量（吨）22,鸡存栏只数23,鸡出栏只数24" + Environment.NewLine +
   "当季的数据、上季的数据名为“查询用表”、“环比用表”的表中" + Environment.NewLine + ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" + Environment.NewLine +
   "用户可以根据上述信息用sql语句编写新的查询公式," + Environment.NewLine +
   " 规模户与全面的审核关系写在了规模户的表中,因此如需只需加406表内审核及环比、与上级逻辑的审核公式即可。例如:" + Environment.NewLine +
   "注意sql预计中的括号和逗号必须在英文状态下输入。字段若有括号,可以在字段两侧加上方括号,例如" + Environment.NewLine +
   "①需要查找有猪出栏但没有猪肉产量的乡镇,sql语句如下:select  县,乡, 生猪出栏头数03,[猪肉产量（吨）14] from 查询用表 where  生猪出栏头数03 > 0 and [猪肉产量（吨）14] = 0 or [猪肉产量（吨）14] is null";
   
            }
        }
        #endregion
        #region 构造函数
        public RelayCommand ModifyFormulaCommand { get; private set; }
        public RelayCommand InsertFormulaCommand { get; private set; }
        public RelayCommand DeleteAddedFormulaCommand { get; private set; }
        public RelayCommand WriteAllFormulasToExcelCommand { get; private set; }
        public Page2ViewModel()
        {
            this.SelectedAnimalReport = new KeyValuePair<string, string>("猪", "审核公式A209");
            // this.SelectedLogical = "表内";
            //  this.SelectedSeries = "核实性";
            ReloadFormulaMsgs();
            this.ModifyFormulaCommand = new RelayCommand(new Action(async () => await SubmitModify()));
            this.InsertFormulaCommand = new RelayCommand(async () => await SubmitAdd());
            this.DeleteAddedFormulaCommand = new RelayCommand(async () => await DeleteAddedFormula());
            this.WriteAllFormulasToExcelCommand = new RelayCommand(async () => await WriteAllFormualsToExcel());
        }


        #endregion
        private async Task SubmitAdd()
        {
            StaticLogger.SystemInfo("执行新增审核公式操作");
            var newFormula = SelectedFormula;
            if (newFormula==null)
            {
                await MyMessagebox.ShowAsyncInMainWindow("没有填写审核公式", Icon.Warning);
                return;
            }
            //利用反射判断newformula对象的字段是否至少有一个不为空
            bool hasNonEmptyProperty = HasNonEmptyProperty(newFormula);
            if (hasNonEmptyProperty == false)//(newFormula.TableRelation ==null && newFormula.Severity == null && newFormula.FormulaEnable == null && newFormula.FormulaName == null && newFormula.FormulaDescription == null&& newFormula.SqlStatement==null && newFormula.AddedByUser==null)
            {
                await MyMessagebox.ShowAsyncInMainWindow("没有选择审核公式", Icon.Warning);
                return;

            }
            else
            {


                bool allPropertiesNonBeNull = AllPropertiesNonBeNull(newFormula);
                if (allPropertiesNonBeNull == false)
                {
                    await MyMessagebox.ShowAsyncInMainWindow("新增审核公式时,需要把所有的空格都填上内容", Icon.Warning);
                    return;


                }
                else
                {
                    if (Regex.IsMatch(newFormula.TableRelation, @"^(表内|环比|与上季度逻辑|规模户与全面)$") == false)
                    {
                        await MyMessagebox.ShowAsyncInMainWindow("表间关系必须是“表内、环比、与上季度逻辑、规模户与全面”其中的一个!", Icon.Warning);
                        return;
                    }
                    else
                    {
                        if (Regex.IsMatch(newFormula.Severity, @"^(强制性|核实性)$") == false)
                        {
                            await MyMessagebox.ShowAsyncInMainWindow("严重度必须是“强制性”和“核实性”中一个!", Icon.Warning);
                            return;
                        }
                        else
                        {
                            if (Regex.IsMatch(newFormula.FormulaEnable, @"^(是|否)$") == false)
                            {
                                await MyMessagebox.ShowAsyncInMainWindow("是否启用必须填“是”或者“否”!", Icon.Warning);
                                return;
                            }
                            else
                            {
                                if (Regex.IsMatch(newFormula.AddedByUser, "是") == false)
                                {
                                    await MyMessagebox.ShowAsyncInMainWindow("用户新增必须填“是”!", Icon.Warning);
                                    return;
                                }
                            }
                        }
                    }
                }
                //MyDBOperator insertOperator = new MyDBOperator();采用单例模式
                MyDBOperator insertOperator = MyDBOperator.DBOperatorInstance;
                //获取本审核公式的表中所有的由用户新增的审核公式,并把名称转成一个集合
                var dtOfAddedFormulas = insertOperator.GetDataTable($"select * from {SelectedAnimalReport.Value} where [用户新增] = '是'");
                List<string> addedFormulaNames = new List<string>();
                if (dtOfAddedFormulas.Rows.Count > 0)
                {
                    addedFormulaNames = dtOfAddedFormulas.AsEnumerable()
                        .Select(r => r.Field<string>("审核公式名称")).ToList();
                    if (addedFormulaNames.Contains(newFormula.FormulaName))
                    {
                        ButtonResult result = await MyMessagebox.ShowAsyncInMainWindow(newFormula.FormulaName + "这条审核公式已经在数据库里了,确认重置吗?", Icon.Info, ButtonEnum.OkCancel);
                        if (result == ButtonResult.Ok)
                        {
                            //执行重置操作
                            insertOperator.ExecuteBySql($"Delete from {SelectedAnimalReport.Value} where 审核公式名称 = '{newFormula.FormulaName}'");
                            string ResetNewFormula = $"insert into [{SelectedAnimalReport.Value}] (表间关系,是否启用,严重度,审核公式名称,审核公式说明,sql语句,用户新增)" +
                   $"  values(\"{newFormula.TableRelation}\",\"{newFormula.FormulaEnable}\",\"{newFormula.Severity}\",\"{newFormula.FormulaName}\",\"{newFormula.FormulaDescription}\",\"{newFormula.SqlStatement}\",\"{newFormula.AddedByUser}\")";

                            insertOperator.ExecuteBySql(ResetNewFormula);
                            await MyMessagebox.ShowAsyncInMainWindow("公式重置成功!", Icon.Warning);
                            ReloadAddNewFormulaMsgs();
                            return;
                        }
                        else//否则返回
                        {
                            return;
                        }
                    }
                }

                //如果数据库中根本没有用户自己的新增公式,那么加进去
                //把内容新增到数据库中
                string updateNewFormula = $"insert into [{SelectedAnimalReport.Value}] (表间关系,是否启用,严重度,审核公式名称,审核公式说明,sql语句,用户新增)" +
                    $"  values(\"{newFormula.TableRelation}\",\"{newFormula.FormulaEnable}\",\"{newFormula.Severity}\",\"{newFormula.FormulaName}\",\"{newFormula.FormulaDescription}\",\"{newFormula.SqlStatement}\",\"{newFormula.AddedByUser}\")";

                insertOperator.ExecuteBySql(updateNewFormula);
                await MyMessagebox.ShowAsyncInMainWindow("公式增加成功!", Icon.Warning);
                ReloadAddNewFormulaMsgs();
            }


        }
        private async Task DeleteAddedFormula()
        {
            StaticLogger.SystemInfo("执行删除自定义审核公式操作");
            var newFormula = SelectedFormula;
            if (newFormula == null)
            {
                await MyMessagebox.ShowAsyncInMainWindow("没有选择审核公式", Icon.Warning);
                return;
            }
            //利用反射判断newformula对象的字段是否至少有一个不为空
            bool hasNonEmptyProperty = HasNonEmptyProperty(newFormula);
            if (hasNonEmptyProperty == false)//(newFormula.TableRelation ==null && newFormula.Severity == null && newFormula.FormulaEnable == null && newFormula.FormulaName == null && newFormula.FormulaDescription == null&& newFormula.SqlStatement==null && newFormula.AddedByUser==null)
            {
                await MyMessagebox.ShowAsyncInMainWindow("没有选择审核公式", Icon.Warning);
                return;

            }
            ButtonResult result = await MyMessagebox.ShowAsyncInMainWindow($"确定要删除名为“{newFormula.FormulaName}”的审核公式吗？", Icon.Info, ButtonEnum.OkCancel);
            if (result == ButtonResult.Ok)
            {
                //执行重置操作
                //var deleteOperator = new MyDBOperator();采用单例模式
                var deleteOperator = MyDBOperator.DBOperatorInstance;
                deleteOperator.ExecuteBySql($"Delete from {SelectedAnimalReport.Value} where 审核公式名称 = '{newFormula.FormulaName}'");
                await MyMessagebox.ShowAsyncInMainWindow("删除成功", Icon.Success);
                ReloadAddNewFormulaMsgs();
            }

        }
        /// <summary>
        /// 如果所有的属性都不为空,返回true,否则返回false
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool AllPropertiesNonBeNull(object obj)
        {
            // 获取对象的类型
            Type type = obj.GetType();

            // 获取所有公共实例属性
            PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);

            // 遍历所有属性
            foreach (PropertyInfo property in properties)
            {
                // 获取属性值
                object propertyValue = property.GetValue(obj);

                // 检查属性值是否为 null
                if (propertyValue == null)
                {
                    return false;
                }
            }

            // 如果所有属性都不为 null，返回 true
            return true;
        }
        /// <summary>
        /// 如果属性全部为空,返回false,否则返回true
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool HasNonEmptyProperty(object obj)
        {
            // 获取对象的类型
            Type type = obj.GetType();

            // 获取所有公共实例属性
            PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);

            // 遍历所有属性
            foreach (PropertyInfo property in properties)
            {
                // 获取属性值
                string propertyValue = property.GetValue(obj) as string;

                // 检查属性值是否不为空
                if (!string.IsNullOrEmpty(propertyValue))
                {
                    return true;
                }
            }

            // 如果所有属性都为空，返回 false
            return false;
        }

        private async Task SubmitModify()
        {
            StaticLogger.SystemInfo("执行修改已有审核公式操作");
            //MyDBOperator updateOperator = new MyDBOperator();改用单例模式
            MyDBOperator updateOperator = MyDBOperator.DBOperatorInstance;
            foreach (var formula in FormulaMsgsCln)
            {

                //if (Regex.IsMatch(formula.关键指标值1, @"^[0-9.<>]+$") == false)
                //{
                //    await MyMessagebox.ShowAsyncInMainWindow($"{formula.审核公式名称}的关键指标值1中含有非法字符,请不要输入除数字小数点和大小号以外的字符!", Icon.Warning);
                //    return;
                //}
                ////再判断关键指标2的值是否报错
                //if (formula.关键指标名称2 == null)
                //{
                //    //执行值替换一个字符串的操作
                //}
                //else
                //{
                //    if (Regex.IsMatch(formula.关键指标值2, @"^[0-9.<>]+$") == false)
                //    {
                //        await MyMessagebox.ShowAsyncInMainWindow($"{formula.审核公式名称}的关键指标值2中含有非法字符,请不要输入除数字小数点和大小号以外的字符!", Icon.Warning);
                //        return;
                //    }
                //    //执行替换两个字符串的操作
                //}
                if ((formula.KeyIndicatorName1 != null && formula.KeyIndicatorValue1 == null) || (formula.KeyIndicatorName2 != null && formula.KeyIndicatorValue2 == null))
                {
                    await MyMessagebox.ShowAsyncInMainWindow("未填写关键指标值！", Icon.Error);
                    return;
                }
                if (Regex.IsMatch(formula.KeyIndicatorValue1, @"^[0-9.<>=是否]+$") == false)
                {
                    await MyMessagebox.ShowAsyncInMainWindow($"{formula.FormulaName}的关键指标值1中含有非法字符,请不要输入除数字小数点和大小号以外的字符!", Icon.Warning);
                    return;
                }
                //再判断关键指标2的值是否报错
                if (formula.KeyIndicatorName2 == null)
                {
                    //执行替换一个字符串的操作
                    //1先把关键指标值的值更新到用户修改的值
                    string updateKeyValue1 = $"update [{SelectedAnimalReport.Value}] set 关键指标值1 = '{formula.KeyIndicatorValue1}' where 审核公式名称 =  '{formula.FormulaName}'";
                    //2.把审核公式说明里面的关键指标名称的值替换为关键指标值里的值
                    string changeFormulaDescription = formula.FormulaPreparedDescription.Replace(formula.KeyIndicatorName1, formula.KeyIndicatorValue1);
                    string updateFormulaDescription = $"update [{SelectedAnimalReport.Value}] set 审核公式说明 =  '{changeFormulaDescription}' where 审核公式名称 = '{formula.FormulaName}'";
                    //3把sql语句的关键指标名称替换为关键指标值的值
                    string changedSqlStatement = formula.SqlPreparedStatement.Replace(formula.KeyIndicatorName1, formula.KeyIndicatorValue1);
                    string updateChangeSqlStatement = $"update [{SelectedAnimalReport.Value}] set sql语句 = '{changedSqlStatement}' where 审核公式名称 = '{formula.FormulaName}'";

                    updateOperator.ExecuteBySql(updateKeyValue1);
                    updateOperator.ExecuteBySql(updateFormulaDescription);
                    updateOperator.ExecuteBySql(updateChangeSqlStatement);
                }
                else
                {
                    if (Regex.IsMatch(formula.KeyIndicatorValue2, @"^[0-9.<>=是否]+$") == false)
                    {
                        await MyMessagebox.ShowAsyncInMainWindow($"{formula.FormulaName}的关键指标值2中含有非法字符,请不要输入除数字小数点和大小号以外的字符!", Icon.Warning);
                        return;
                    }
                    //执行替换两个字符串的操作
                    //1.先把关键指标值的数据更新为用户修改的值

                    string updateKeyValuesSql = $"update [{SelectedAnimalReport.Value}] set 关键指标值1 = '{formula.KeyIndicatorValue1}',关键指标值2 = '{formula.KeyIndicatorValue2}' where 审核公式名称 =  '{formula.FormulaName}'";
                    //2.把审核公式说明里面的关键指标名称替换为关键指标值
                    string changeFormulaDescription = formula.FormulaPreparedDescription.Replace(formula.KeyIndicatorName1, formula.KeyIndicatorValue1);
                    changeFormulaDescription = changeFormulaDescription.Replace(formula.KeyIndicatorName2, formula.KeyIndicatorValue2);
                    string updateFormulaDescription = $"update [{SelectedAnimalReport.Value}] set 审核公式说明 =  '{changeFormulaDescription}' where 审核公式名称 = '{formula.FormulaName}'";
                    //3.把sql语句中的关键指标名称替换为关键指标值 
                    string changedSqlStatement = formula.SqlPreparedStatement.Replace(formula.KeyIndicatorName1, formula.KeyIndicatorValue1);
                    changedSqlStatement = changedSqlStatement.Replace(formula.KeyIndicatorName2, formula.KeyIndicatorValue2);
                    string updateChangeSqlStatement = $"update [{SelectedAnimalReport.Value}] set sql语句 = '{changedSqlStatement}' where 审核公式名称 = '{formula.FormulaName}'";
                    //执行三条语句
                    updateOperator.ExecuteBySql(updateKeyValuesSql);
                    updateOperator.ExecuteBySql(updateFormulaDescription);
                    updateOperator.ExecuteBySql(updateChangeSqlStatement);
                }
            }
            ReloadFormulaMsgs();
            await MyMessagebox.ShowAsyncInMainWindow("修改完成!", Icon.Success);
        }
      
        //private static readonly string
        //   _dbPath = Path.Combine(MyDataFolderOperator.AuditProgramFolderPath, "畜牧业数据库.db");
        //private void UpdateFormulaInDB()
        //{
        //    MyDBOperator updateOperator = new MyDBOperator();

        //    using (SqliteConnection conn = new SqliteConnection($"Data Source={_dbPath}"))
        //    {
        //        conn.Open();
        //        string sqlToUpdate = $"select 审核公式名称,审核公式说明,关键指标名称1,关键指标值1,关键指标名称2,关键指标值2 from [{SelectedAnimalReport.Value}] where [关键指标名称1] is not null";
        //        using (SqliteCommand command = new SqliteCommand(sqlToUpdate,conn))
        //        {
        //            using (SqliteDataReader reader = command.ExecuteReader())
        //            {
        //                while (reader.Read())
        //                {
        //                    //没写完
        //                }
        //            }
        //        }
        //    }
        //}

        private async Task WriteAllFormualsToExcel()
        {
            StaticLogger.SystemInfo("执行导出已有审核公式操作");
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (var excelPackage = new ExcelPackage())
            {
                string[] formulaTableNames = new string[] { 
                "审核公式A209","审核公式A210","审核公式A211","审核公式A212","审核公式A406"
                };

                foreach (string formulaTableName in formulaTableNames)
                {
                    // MyDBOperator myDBOperator = new MyDBOperator();改用单例模式
                    MyDBOperator myDBOperator = MyDBOperator.DBOperatorInstance;
                    DataTable dt = myDBOperator.GetDataTable($"select * from {formulaTableName} where  是否启用 ='是' or ( 是否启用 = '否' and 专属地市 is not null ) order by 严重度");
                    if (dt==null||dt.Rows.Count==0)
                    {
                        continue;
                    }
                    var sht = excelPackage.Workbook.Worksheets.Add(formulaTableName);
                    //写入表头
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        sht.Cells[1,i+1].Value= dt.Columns[i].ColumnName;
                    }
                    //写入数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            sht.Cells[i + 2, j + 1].Value = dt.Rows[i][j];
                        }
                    }
                }



                var mainWindow = GetMainWindow();
                var topLevel = TopLevel.GetTopLevel(mainWindow);
                // 启动异步操作以打开对话框。
                var file = await topLevel.StorageProvider.SaveFilePickerAsync(new FilePickerSaveOptions
                {
                    Title = "保存文件",
                    SuggestedFileName = "数据库中在使用的审核公式.xlsx",
                   //SuggestedStartLocation = MyDataFolderOperator.AuditOutcomeFolderPathInAuditProgramFolder,
                });


                if (file !=null)
                {
                    using (var steam = await file.OpenWriteAsync())
                    {
                        excelPackage.SaveAs(steam);
                    }
                    await MyMessagebox.ShowAsyncInMainWindow("数据库中正在使用的公式保存成功", Icon.Success);
                }
                //FileInfo excelFileInfo = new FileInfo(Path.Combine(MyDataFolderOperator.AuditOutcomeFolderPathInAuditProgramFolder, "数据库中在使用的审核公式.xlsx"));

                //excelPackage.SaveAs(excelFileInfo);
               

            }

        }
        private Window GetMainWindow()
        {
            if (Application.Current?.ApplicationLifetime is IClassicDesktopStyleApplicationLifetime desktop)
            {
                return desktop.MainWindow;
            }
            return null;
        }
        public void ShowFromulaMsgs()
        {
            MyMessagebox.ShowAsyncInMainWindow(FormulaMsgsCln[0].KeyIndicatorValue1, Icon.Info);
        }
    }
}
